﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml.Linq;
using Common;
using DO.Clustering;

namespace DAL.Clustering
{
    public class ClusteringDALC
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connName"></param>
        /// <param name="resultTable"></param>
        /// <param name="taskField"></param>
        /// <param name="dataField"></param>
        /// <param name="taskName"></param>
        /// <returns></returns>
        public static DocumentClusteringOutput GetClusteringResult(
            string connName, string resultTable, string taskField, 
            string dataField, string taskName)
        {
            string connStr = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format(
                    "select {0} from {1} where {2}='{3}'",
                    dataField, resultTable, taskField, taskName);
                SqlDataReader reader = cmd.ExecuteReader();
                string encodedOutput = string.Empty;
                if (reader.Read())
                    encodedOutput = reader.GetString(0);
                reader.Close();
                DocumentClusteringOutput output=new DocumentClusteringOutput();
                if (!string.IsNullOrEmpty(encodedOutput))
                {
                    string xml = Base64Util.DecodeAsString(encodedOutput, false);
                    XDocument xDoc = XDocument.Parse(xml);
                    XElement xe = xDoc.Root;
                    output = (DocumentClusteringOutput) output.Instantiate(xe);
                }
                return output;
            }
        }

        public static Dictionary<int, string> GetDocIDTitlesFromSQL(
            string connStr, string sql, string docIDField, string docTitleField)
        {
            Dictionary<int, string> docTitles = new Dictionary<int, string>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader[docIDField] != DBNull.Value)
                    {
                        int docID = (int)reader[docIDField];
                        string title = string.Empty;
                        if (reader[docTitleField] != DBNull.Value)
                            title = reader[docTitleField].ToString();
                        if (!docTitles.ContainsKey(docID))
                            docTitles.Add(docID, title);
                    }
                }
                reader.Close();
            }
            return docTitles;
        }

        public static Dictionary<int, Dictionary<int, int>> GetDocTermFreqs(
            string connStr,
            string termDB,
            string termTable,
            string termIDFieldInTermTable,
            string phraseFieldInTermTable,
            string termFreqTable,
            string projIDFieldInFreqTable,
            string termIDFieldInFreqTable,
            string termFreqFieldInFreqTable,
            List<int> docIDs,
            ref Dictionary<int, string> terms)
        {
            Dictionary<int, Dictionary<int, int>> docTermFreqs = new Dictionary<int, Dictionary<int, int>>();
            Regex dbRegex = new Regex("Database=([^;\\s]+)", RegexOptions.IgnoreCase);
            if (dbRegex.IsMatch(connStr) && !string.IsNullOrEmpty(termDB))
            {
                Match dbMatch = dbRegex.Match(connStr);
                string oldDbName = dbMatch.Groups[1].Value;
                connStr = connStr.Replace(oldDbName, termDB);
            }

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader;
                foreach (int docID in docIDs)
                {
                    string sql = string.Format(
                        "select t2.{4}, t1.{2}, t2.{5}, t1.{6} " +
                        "from {0} t1 inner join {1} t2 on t1.{2}=t2.{3} " +
                        "where t2.{4}={7}",
                        termTable, termFreqTable,
                        termIDFieldInTermTable,
                        termIDFieldInFreqTable,
                        projIDFieldInFreqTable,
                        termFreqFieldInFreqTable,
                        phraseFieldInTermTable, docID);
                    cmd.CommandText = sql;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        int termID = reader.GetInt32(1);
                        int termFreq = reader.GetInt32(2);
                        string phrase = reader.GetString(3);
                        if (!terms.ContainsKey(termID))
                            terms.Add(termID, phrase);
                        if (docTermFreqs.ContainsKey(docID))
                        {
                            Dictionary<int, int> termFreqs = docTermFreqs[docID];
                            termFreqs.Add(termID, termFreq);
                            docTermFreqs[docID] = termFreqs;
                        }
                        else
                        {
                            Dictionary<int, int> termFreqs = new Dictionary<int, int>();
                            termFreqs.Add(termID, termFreq);
                            docTermFreqs.Add(docID, termFreqs);
                        }
                    }
                    reader.Close();
                }
            }
            return docTermFreqs;
        }

        public static void SaveClusteringOutput(
            DocumentClusteringOutput output,
            string connStr, string outputDB, string tableName, 
            string taskField, string dataField)
        {
            output = CompressOutput(output);
            Regex dbRegex = new Regex("Database=([^;\\s]+)", RegexOptions.IgnoreCase);
            if (dbRegex.IsMatch(connStr) && !string.IsNullOrEmpty(outputDB))
            {
                Match dbMatch = dbRegex.Match(connStr);
                string oldDbName = dbMatch.Groups[1].Value;
                connStr = connStr.Replace(oldDbName, outputDB);
            }
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                string encodedOutput = Base64Util.EncodeAsBase64(output.Serialize().ToString(), false);
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql =
                    @"if exists (select * from {0} r where r.{1}='{3}')
	delete r from {0} r where r.{1}='{3}';
insert into {0} ({1},{2}) values (
	'{3}',
	'{4}');";
                sql = string.Format(
                    sql, tableName, taskField, dataField, output.TaskName, encodedOutput);
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

        public static List<string> ListClusteringOutputs(
            string connName, string resultTable, string taskNameField)
        {
            string connStr = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
            List<string> outputs=new List<string>();
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("select {0} from {1}", taskNameField, resultTable);
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    outputs.Add(reader.GetString(0));
                }
                reader.Close();
            }
            return outputs;
        }

        public static void DeleteClusteringResult(
            string connStr, string resultTable, string taskField,
            string dataField, string taskName)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format(
                    "delete t from {0} t where t.{1}='{2}'",
                    resultTable, taskField, taskName);
                cmd.ExecuteNonQuery();
            }
        }

        public static void RenameClusteringResult(string connStr, string resultTable, string taskField,
            string oldName, string newName)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = @"if not exists (select * from {0} t where t.{1}='{3}')
update t
set t.{1}='{3}'
from {0} t
where t.{1}='{2}'";
                sql = string.Format(sql, resultTable, taskField, oldName, newName);
                cmd.CommandText = string.Format(
                    "update t from {0} t where set t.{1}='{2}' where t.{1}='{3}'",
                    resultTable, taskField, newName, oldName);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// only save top 100 terms
        /// </summary>
        /// <param name="output"></param>
        /// <returns></returns>
        private static DocumentClusteringOutput CompressOutput(DocumentClusteringOutput output)
        {
            List<Feature> features=new List<Feature>();
            foreach(int termID in output.TermWeights.Keys)
            {
                Feature feat=
                    new Feature()
                        {
                            FeatureID=termID,
                            Name=output.AllTerms[termID],
                            Weight=output.TermWeights[termID]
                        };
                features.Add(feat);
            }
            features.Sort();
            List<int> top100TermIDs=new List<int>();
            int idx = 0;
            foreach(Feature feat in features)
            {
                top100TermIDs.Add(feat.FeatureID);
                idx++;
                if(idx>=100)
                    break;
            }
            int[] allTermIDs=new int[output.AllTerms.Count];
            output.AllTerms.Keys.CopyTo(allTermIDs, 0);
            foreach(int termID in allTermIDs)
            {
                if (!top100TermIDs.Contains(termID))
                {
                    output.AllTerms.Remove(termID);
                    output.TermWeights.Remove(termID);
                }
            }
            int[] docIDs=new int[output.DocTermFreqs.Count];
            output.DocTermFreqs.Keys.CopyTo(docIDs,0);
            foreach (int docID in docIDs)
            {
                Dictionary<int, int> termFreqs = output.DocTermFreqs[docID];
                int[] termIDs = new int[termFreqs.Count];
                termFreqs.Keys.CopyTo(termIDs, 0);
                foreach (int termID in termIDs)
                {
                    if (!top100TermIDs.Contains(termID))
                        termFreqs.Remove(termID);
                }
                output.DocTermFreqs[docID] = termFreqs;
            }

            return output;
        }
    }
}
